﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_REF_REPORT_Ref2011r_Get_Total_Chart')
	BEGIN
		DROP Procedure usp_UPDMS_REF_REPORT_Ref2011r_Get_Total_Chart
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_REF_REPORT_Ref2011r_Get_Total_Chart
**	Desc : 인원별, 업소별, 요일별 금액, 방문수를 조회한다.(바챠트, 방사챠트)
**	Test Exec Query : Exec usp_UPDMS_REF_REPORT_Ref2011r_Get_Total_Chart '', '', '2004-01-01', '2013-05-01','','','Total_Cnt','ko'
**	Called by : Ref_Dac_UPDMS_REF_REPORT_Ref2011r.cs
**	Program ID : Ref2011r
**	Auth : 송시명
**	Date : 2013-05-09
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_REF_REPORT_Ref2011r_Get_Total_Chart]
@ls_shop_id nvarchar(max),
@ls_nick_nm nvarchar(200),
@ls_tour_dt_from nvarchar(10),
@ls_tour_dt_to nvarchar(10),
@ls_homerun_yn nvarchar(5),
@ls_contents nvarchar(max),
@ls_order_by nvarchar(100),
@ls_lang_set nvarchar(2)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @shop_ids nvarchar(max)
DECLARE @sql nvarchar(max)

IF @ls_shop_id = ''
BEGIN
    SET @shop_ids = '%'
END
ELSE
BEGIN
    SET @shop_ids = @ls_shop_id
END

SET @sql = ''
SET @sql = @sql + '
/* 인원별 방문 */
SELECT TOP 20 urs.Shop_Nm,
       urt.Nick_Nm,
       SUM(urt.Price) AS Total_Price,
       COUNT(urt.Seq) AS Total_Cnt
  FROM UPDMS_REF_TOUR AS urt WITH(NOLOCK)
  JOIN UPDMS_REF_SHOP AS urs WITH(NOLOCK) ON urt.Shop_Id = urs.Shop_Id
 WHERE ( urt.Shop_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@shop_ids))
       OR urt.Shop_Id LIKE @shop_ids
       )
   AND urt.Nick_Nm LIKE ''%'' + @ls_nick_nm + ''%''
   AND urt.Tour_Dt >= @ls_tour_dt_from
   AND urt.Tour_Dt <= @ls_tour_dt_to
   AND urt.HomeRun_Yn LIKE @ls_homerun_yn + ''%''
   AND urt.Contents LIKE ''%'' + @ls_contents + ''%''
 GROUP BY urt.Shop_Id, urs.Shop_Nm, urt.Nick_Nm
 ORDER BY ' + @ls_order_by + ' DESC


/* 업소별 방문 */
SELECT TOP 20 urs.Shop_Nm,
       SUM(urt.Price) AS Total_Price,
       COUNT(urt.Seq) AS Total_Cnt
  FROM UPDMS_REF_TOUR AS urt WITH(NOLOCK)
  JOIN UPDMS_REF_SHOP AS urs WITH(NOLOCK) ON urt.Shop_Id = urs.Shop_Id
 WHERE ( urt.Shop_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@shop_ids))
       OR urt.Shop_Id LIKE @shop_ids
       )
   AND urt.Nick_Nm LIKE ''%'' + @ls_nick_nm + ''%''
   AND urt.Tour_Dt >= @ls_tour_dt_from
   AND urt.Tour_Dt <= @ls_tour_dt_to
   AND urt.HomeRun_Yn LIKE @ls_homerun_yn + ''%''
   AND urt.Contents LIKE ''%'' + @ls_contents + ''%''
 GROUP BY urt.Shop_Id, urs.Shop_Nm
 ORDER BY ' + @ls_order_by + ' DESC
'

DECLARE @param nvarchar(max)
SET @param = '
  @shop_ids nvarchar(max),
  @ls_nick_nm nvarchar(200),
  @ls_tour_dt_from nvarchar(10),
  @ls_tour_dt_to nvarchar(10),
  @ls_homerun_yn nvarchar(5),
  @ls_contents nvarchar(max)
'
--print (@sql)
EXEC sp_executesql @sql, @param,
                   @shop_ids = @shop_ids,
                   @ls_nick_nm = @ls_nick_nm,
                   @ls_tour_dt_from = @ls_tour_dt_from,
                   @ls_tour_dt_to = @ls_tour_dt_to,
                   @ls_homerun_yn = @ls_homerun_yn,
                   @ls_contents = @ls_contents


/* 요일별 방문 */
SELECT z.Week_Day,
       SUM(ISNULL(z.Total_Price, 0)) AS Total_Price,
       SUM(ISNULL(z.Total_Cnt, 0)) AS Total_Cnt
  FROM (
       SELECT umd.Dt as Tour_Dt,
              CASE @ls_lang_set WHEN 'ko' THEN umd.Day_K_Abb_Nm ELSE umd.Day_E_Abb_Nm END Week_Day,
              urt.Total_Price,
              urt.Total_Cnt,
              umd.View_Order
         FROM UPDMS_MST_DATE umd WITH(NOLOCK)
         LEFT OUTER JOIN
              (
              SELECT urt.Tour_Dt,
                     SUM(urt.Price) AS Total_Price,
                     COUNT(urt.Seq) AS Total_Cnt
                FROM UPDMS_REF_TOUR AS urt WITH(NOLOCK)
               WHERE ( urt.Shop_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@shop_ids))
                     OR urt.Shop_Id LIKE @shop_ids
                     )
                 AND urt.Nick_Nm LIKE '%' + @ls_nick_nm + '%'
                 AND urt.Tour_Dt >= @ls_tour_dt_from
                 AND urt.Tour_Dt <= @ls_tour_dt_to
                 AND urt.HomeRun_Yn LIKE @ls_homerun_yn + '%'
                 AND urt.Contents LIKE '%' + @ls_contents + '%'
               GROUP BY urt.Tour_Dt
              ) urt
           ON umd.Dt = urt.Tour_Dt
        WHERE umd.Dt >= @ls_tour_dt_from
          AND umd.Dt <= @ls_tour_dt_to 
       ) z
 GROUP BY z.Week_Day
 ORDER BY MAX(z.View_Order)


/* 인원별 홈런 */
SELECT urs.Shop_Nm,
       urt.Nick_Nm,
       COUNT(urt.HomeRun_Yn) AS HomeRun_Cnt
  FROM UPDMS_REF_TOUR AS urt WITH(NOLOCK)
  JOIN UPDMS_REF_SHOP AS urs WITH(NOLOCK) ON urt.Shop_Id = urs.Shop_Id
 WHERE ( urt.Shop_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@shop_ids))
       OR urt.Shop_Id LIKE @shop_ids
       )
   AND urs.Category <> 'AB003' --안마제외
   AND urt.Nick_Nm LIKE '%' + @ls_nick_nm + '%'
   AND urt.Tour_Dt >= @ls_tour_dt_from
   AND urt.Tour_Dt <= @ls_tour_dt_to   
   AND urt.Contents LIKE '%' + @ls_contents + '%'
   AND ISNULL(urt.HomeRun_Yn, 'N') = 'Y'
 GROUP BY urt.Shop_Id, urs.Shop_Nm, urt.Nick_Nm
 ORDER BY HomeRun_Cnt DESC


/* 업소별 홈런 */
SELECT urs.Shop_Nm,
       COUNT(urt.HomeRun_Yn) AS HomeRun_Cnt
  FROM UPDMS_REF_TOUR AS urt WITH(NOLOCK)
  JOIN UPDMS_REF_SHOP AS urs WITH(NOLOCK) ON urt.Shop_Id = urs.Shop_Id
 WHERE ( urt.Shop_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@shop_ids))
       OR urt.Shop_Id LIKE @shop_ids
       )
   AND urs.Category <> 'AB003' --안마제외
   AND urt.Nick_Nm LIKE '%' + @ls_nick_nm + '%'
   AND urt.Tour_Dt >= @ls_tour_dt_from
   AND urt.Tour_Dt <= @ls_tour_dt_to
   AND ISNULL(urt.HomeRun_Yn, 'N') = 'Y'
   AND urt.Contents LIKE '%' + @ls_contents + '%'
 GROUP BY urt.Shop_Id, urs.Shop_Nm
 ORDER BY HomeRun_Cnt DESC


/* 업소별 홈런율 */
SELECT z.Shop_Nm,
       z.HomeRun_Cnt,
       z.Total_Cnt,
       z.HomeRun_Avg
  FROM (
       SELECT urs.Shop_Nm,
              SUM(CASE urt.HomeRun_Yn WHEN 'Y' THEN 1 ELSE 0 END) AS HomeRun_Cnt,
              COUNT(urt.Seq) AS Total_Cnt,
              ROUND(ROUND(CONVERT(FLOAT,(SUM(CASE urt.HomeRun_Yn WHEN 'Y' THEN 1 ELSE 0 END))),2,1) / COUNT(urt.Seq) * 100,2,1) AS HomeRun_Avg
         FROM UPDMS_REF_TOUR AS urt WITH(NOLOCK)
         JOIN UPDMS_REF_SHOP AS urs WITH(NOLOCK) ON urt.Shop_Id = urs.Shop_Id
        WHERE ( urt.Shop_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@shop_ids))
              OR urt.Shop_Id LIKE @shop_ids
              )
          AND urs.Category <> 'AB003' --안마제외
          AND urt.Nick_Nm LIKE '%' + @ls_nick_nm + '%'
          AND urt.Tour_Dt >= @ls_tour_dt_from
          AND urt.Tour_Dt <= @ls_tour_dt_to
          AND urt.Contents LIKE '%' + @ls_contents + '%'
        GROUP BY urt.Shop_Id, urs.Shop_Nm
       ) z
 WHERE z.HomeRun_Cnt > 0
 ORDER BY z.HomeRun_Avg DESC


/* 최근방문정보 */
SELECT ROW_NUMBER() OVER(ORDER BY Shop_Nm, Nick_Nm, Tour_Dt) AS Rnum,
       Shop_Id,
       Shop_Nm,
       Nick_Nm,
       CASE Partition_Num WHEN 1 THEN '<b class="b">' + CONVERT(NVARCHAR, Cnt) + '</b>' ELSE CONVERT(NVARCHAR, Cnt) END Cnt,
       Tour_Dt,
       Attach_File_Info
  FROM (
       SELECT Shop_Id,
              Shop_Nm,
              Nick_Nm,
              DATEDIFF(DAY, CONVERT(DATETIME, Tour_Dt), GETDATE()) AS Cnt,
              Tour_Dt,
              Attach_File_Info,
              Partition_Num = ROW_NUMBER() OVER(PARTITION BY Shop_Nm, Nick_Nm ORDER BY Tour_Dt DESC)
         FROM (
              SELECT urt.Shop_Id,
                     urs.Shop_Nm,
                     urt.Nick_Nm,
                     urt.Tour_Dt,
                     CASE WHEN dbo.ufn_UPDMS_Get_Exist_Attach_File_Image(urt.Seq, 'Ref1012m') <= 0 THEN ''
                          ELSE '<div title="첨부파일" class="sbtn sbtn_fdisk" onclick="popupMultiImage(''' + CONVERT(VARCHAR, urt.Seq) + ''',''Ref1012m'');"></div>'
                     END Attach_File_Info
                FROM UPDMS_REF_TOUR AS urt WITH(NOLOCK)
                JOIN UPDMS_REF_SHOP AS urs WITH(NOLOCK) ON urt.Shop_Id = urs.Shop_Id
               WHERE urt.Shop_Id + '_' + Nick_Nm IN (
				       SELECT Attr1 + '_' + Attr2
                         FROM UPDMS_COM_CD WITH(NOLOCK)
                        WHERE Id = 'REF'
                          AND Cd LIKE 'AC%'
                          AND Use_Yn = 'Y'
                     )
              ) A
       ) B
 WHERE Partition_Num <= CONVERT(INT, (dbo.ufn_UPDMS_Get_Setting_Value('REF_Opt_03')))


/* 시간대별 방문 */
DECLARE @TMP_UPDMS_MST_HOUR TABLE (
  [Hour] NVARCHAR(2),
  [View_Order] CHAR(1)
)

INSERT @TMP_UPDMS_MST_HOUR
SELECT '00' AS [Hour], 'A' AS [View_Order] UNION ALL
SELECT '01', 'B' UNION ALL
SELECT '02', 'C' UNION ALL
SELECT '03', 'D' UNION ALL
SELECT '04', 'E' UNION ALL
SELECT '05', 'F' UNION ALL
SELECT '06', 'G' UNION ALL
SELECT '07', 'H' UNION ALL
SELECT '08', 'I' UNION ALL
SELECT '09', 'J' UNION ALL
SELECT '10', 'K' UNION ALL
SELECT '11', 'L' UNION ALL
SELECT '12', 'M' UNION ALL
SELECT '13', 'N' UNION ALL
SELECT '14', 'O' UNION ALL
SELECT '15', 'P' UNION ALL
SELECT '16', 'Q' UNION ALL
SELECT '17', 'R' UNION ALL
SELECT '18', 'S' UNION ALL
SELECT '19', 'T' UNION ALL
SELECT '20', 'U' UNION ALL
SELECT '21', 'V' UNION ALL
SELECT '22', 'W' UNION ALL
SELECT '23', 'X'

SELECT A.Hour,
       ISNULL(Z.Total_Cnt, 0) AS Total_Cnt,
       ISNULL(Z.Total_Price, 0) AS Total_Price
  FROM @TMP_UPDMS_MST_HOUR A
  LEFT OUTER JOIN
       (
       SELECT Tour_Hour,
              COUNT(Seq) AS Total_Cnt,
              SUM(Price) AS Total_Price
         FROM UPDMS_REF_TOUR WITH(NOLOCK)
   	    WHERE Nick_Nm LIKE '%' + @ls_nick_nm + '%'
          AND Tour_Dt >= @ls_tour_dt_from
          AND Tour_Dt <= @ls_tour_dt_to
          AND HomeRun_Yn LIKE @ls_homerun_yn + '%'
          AND Contents LIKE '%' + @ls_contents + '%'
        GROUP BY Tour_Hour
       ) Z
    ON A.Hour = Z.Tour_Hour
 ORDER BY A.View_Order

GO